/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DataAccess;

import BusinessLogic.*;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Vector;
import javax.swing.table.TableModel;

/**
 *
 * @author MrToan
 */
public class SalesMenFunction_DA {

    public TableModel getUserTableValue() {
        SetTableModel rtm = new SetTableModel();
        String Query = "select * from Customers where Status=1";
        try {
            rtm.setHostURL(ConnectionDB.getConnectionString());
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        rtm.setQuery(Query);
        return rtm;
    }

    public TableModel getItemTableValue() {
        SetTableModel rtm = new SetTableModel();
        String Query = "select * from Items";
        try {
            rtm.setHostURL(ConnectionDB.getConnectionString());
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        rtm.setQuery(Query);
        return rtm;
    }

    public Vector getCate() {
        Vector CateList = new Vector();

        ResultSet RS = null;
        try {
            CallableStatement smt = ConnectionDB.getConnection().prepareCall("{call GetCategory}");
            RS = smt.executeQuery();
            while (RS.next()) {
                CateList.add(RS.getString("C_Name"));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return CateList;
    }

    public Vector getCateChanged() {
        Vector CateList = new Vector();
        ResultSet RS = null;
        try {
            CallableStatement smt = ConnectionDB.getConnection().prepareCall("{call changeCategory}");
            RS = smt.executeQuery();
            while (RS.next()) {
                CateList.add(RS.getString("C_Name"));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return CateList;
    }

    public TableModel getBillTableValue(String Date) {
        SetTableModel rtm = new SetTableModel();
        String Query = "select * from Bills where Status=1 and Date='" + Date + "'";
        try {
            rtm.setHostURL(ConnectionDB.getConnectionString());
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        rtm.setQuery(Query);
        return rtm;
    }

    public int AddBill(String CID, String Date, String UID, String Payment, float Discount, int Status, float Total) {
        int InsertSuccess = 0;
        try {
            CallableStatement smt = ConnectionDB.getConnection().prepareCall("{call AddBill(?,?,?,?,?,?,?)}");
            smt.setString(1, UID);
            smt.setString(2, CID);
            smt.setString(3, Date);
            smt.setFloat(4, Discount);
            smt.setString(5, Payment);
            smt.setFloat(6, Total);
            smt.setInt(7, Status);
            InsertSuccess = smt.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return InsertSuccess;
    }

    public int getBID() {
        int BID = 0;

        ResultSet RS = null;
        try {
            CallableStatement smt = ConnectionDB.getConnection().prepareCall("{call getBillID}");
            RS = smt.executeQuery();
            while (RS.next()) {
                BID = RS.getInt("CountID");
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return BID;
    }

    public TableModel getItemChanged(String cateChanged) {
        SetTableModel rtm = new SetTableModel();
        String Query = null;
        if (cateChanged.equals("-- All Categories --")) {
            Query = "select * from Items";
            try {
                rtm.setHostURL(ConnectionDB.getConnectionString());
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        } else {
            Query = "select i.I_ID,c.C_ID,i.I_Name,i.I_Name,i.I_Unit,i.I_Quantity,i.I_Price,i.I_Status from Items i inner join Categories c on c.C_ID=i.C_ID where c.C_Name='" + cateChanged + "'";
            try {
                rtm.setHostURL(ConnectionDB.getConnectionString());
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        rtm.setQuery(Query);
        return rtm;
    }

    public TableModel getItemtoTable(String itemName, String cateName) {
        SetTableModel tableModel = new SetTableModel();
        String Query = null;

        if (itemName.equals("") && cateName.equals("-- All Categories --")) {
            Query = "select * from Items";
            try {
                tableModel.setHostURL(ConnectionDB.getConnectionString());
            } catch (Exception ex) {
                ex.printStackTrace();
            }

        } else if (!itemName.equals("") && cateName.equals("-- All Categories --")) {
            Query = "select i.I_ID,c.C_ID,i.I_Name,i.I_Name,i.I_Unit,i.I_Quantity,i.I_Price,i.I_Status from Items i inner join Categories c on c.C_ID=i.C_ID where i.I_Name like '" + itemName + "%'";
            try {
                tableModel.setHostURL(ConnectionDB.getConnectionString());
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        } else {
            Query = "select i.I_ID,c.C_ID,i.I_Name,i.I_Name,i.I_Unit,i.I_Quantity,i.I_Price,i.I_Status from Items i inner join Categories c on c.C_ID=i.C_ID where i.I_Name like '" + itemName + "%' and c.C_Name='" + cateName + "'";
            try {
                tableModel.setHostURL(ConnectionDB.getConnectionString());
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        tableModel.setQuery(Query);
        return tableModel;
    }

    public int addNewCustomer_DA(String custName, String email, String address, String tel, boolean gender, String city, String country, int status, float purchar) {
        int respond = 0;
        try {
            CallableStatement cs = ConnectionDB.getConnection().prepareCall("{call sp_addCustomer(?,?,?,?,?,?,?,?,?)}");
            cs.setString(1, custName);
            cs.setString(2, email);
            cs.setString(3, address);
            cs.setString(4, tel);
            cs.setBoolean(5, gender);
            cs.setString(6, city);
            cs.setString(7, country);
            cs.setInt(8, status);
            cs.setFloat(9, purchar);
            respond = cs.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return respond;
    }

    public int getCID() {
        int respond = 0;
        ResultSet RS = null;
        try {
            CallableStatement cs = ConnectionDB.getConnection().prepareCall("{call sp_CustID}");
            RS = cs.executeQuery();
            while (RS.next()) {
                respond = RS.getInt("countCustomer");
            }
        } catch (SQLException ex) {
        }
        return respond;
    }

    public int deleteCustomer_DA(int custID) {
        int respond = 0;
        try {
            CallableStatement deleteCust = ConnectionDB.getConnection().prepareCall("{call sp_deleteCustomer(?)}");
            deleteCust.setInt(1, custID);
            respond = deleteCust.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return respond;
    }

    public int updateCustomer_DA(int custID, String custName, String email, String address, String tel, boolean gender, String city, String country, int status, float purchar) {
        int respond = 0;
        try {
            CallableStatement updateCust = ConnectionDB.getConnection().prepareCall("{call sp_updateCustomer(?,?,?,?,?,?,?,?,?,?)}");
            updateCust.setInt(1, custID);
            updateCust.setString(2, custName);
            updateCust.setString(3, email);
            updateCust.setString(4, address);
            updateCust.setString(5, tel);
            updateCust.setBoolean(6, gender);
            updateCust.setString(7, city);
            updateCust.setString(8, country);
            updateCust.setInt(9, status);
            updateCust.setFloat(10, purchar);
            respond = updateCust.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return respond;
    }

    public Item getItemInfor_DA(String itemID) {

        Item item = null;
        int id = 0;
        ResultSet rsItems = null;
        try {
            CallableStatement getItemInfor = ConnectionDB.getConnection().prepareCall("{call sp_itemInfor(?)}");
            getItemInfor.setString(1, itemID);
            rsItems = getItemInfor.executeQuery();
            while (rsItems.next()) {
                item = new Item(rsItems.getString("I_ID"), rsItems.getInt("C_ID"), rsItems.getString("I_Name"), rsItems.getString("I_Unit"), rsItems.getInt("I_Quantity"), rsItems.getFloat("I_Price"), rsItems.getInt("I_Status"));
            }

        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return item;
    }

    public int addNewBill_DA(Bill bill, ArrayList<BillDetails> billDetails) {
        int respond = 0;
        try {
            CallableStatement addNewBill = ConnectionDB.getConnection().prepareCall("{call sp_addNewBill(?,?,?,?,?,?,?)}");
            addNewBill.setString(1, bill.getU_ID());
            addNewBill.setInt(2, bill.getCustID());
            addNewBill.setString(3, bill.getDate());
            addNewBill.setFloat(4, bill.getDiscount());
            addNewBill.setString(5, bill.getPayment());
            addNewBill.setFloat(6, bill.getTotal());
            addNewBill.setBoolean(7, bill.isStatus());
            respond = addNewBill.executeUpdate();
            ConnectionDB.exec("update Customers set Purchased=Purchased+" + bill.getTotal() + " where CustID=" + bill.getCustID());
            for (BillDetails bd : billDetails) {
                insertBillDetail(bill, bd);
            }
            System.out.println(""+bill.getU_ID());
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return respond;
    }

    public boolean insertBillDetail(Bill bill,BillDetails details){
        String sql="insert into BillDetails values(?,?,?,?)";
        try {
            PreparedStatement pst = ConnectionDB.getConnection().prepareStatement(sql);
            pst.setInt(1,bill.getB_ID());
            pst.setString(2,details.getItemID());
            pst.setInt(3,details.getQuantity());
            pst.setFloat(4,details.getPrice());
            if(pst.executeUpdate()==0)
            {
                return false;                
            }
            ConnectionDB.exec("update Items set I_Quantity=I_Quantity-"+details.getQuantity()+" where I_ID='"+details.getItemID()+"'");
        } catch (SQLException ex) {
         System.out.println(ex.getMessage());
        }
        return true;
    }

    public TableModel tableBillList_DA() {
        SetTableModel rtm = new SetTableModel();
        String Query = "select * from Bills where Status = 1 Order by Date desc";
        try {
            rtm.setHostURL(ConnectionDB.getConnectionString());
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        rtm.setQuery(Query);
        return rtm;
    }

    public TableModel getBillDetails_DA(int B_ID) {
         SetTableModel rtm = new SetTableModel();
        String Query = "select * from BillDetails where B_ID="+B_ID;
        try {
            rtm.setHostURL(ConnectionDB.getConnectionString());
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        rtm.setQuery(Query);
        return rtm;
    }

    public int cancelBill_DA(int billDA) {
        int cancel=0;
        try {
            CallableStatement cancelBill = ConnectionDB.getConnection().prepareCall("{call sp_cancelBill(?)}");
            cancelBill.setInt(1, billDA);
            cancel = cancelBill.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return cancel;
    }

    public int CountCategory()
    {
        int count=0;
        ResultSet RS;
        try {
            CallableStatement smt = ConnectionDB.getConnection().prepareCall("{call getCategoryID}");
            RS=smt.executeQuery();
            while(RS.next())
            {
                count=RS.getInt(1);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return count;
    }


    public int getTotalItems() {
        int totalItems=0;
        ResultSet rs=null;
        try {
            CallableStatement getTotalItems = ConnectionDB.getConnection().prepareCall("{call sp_getTotalItems}");
            rs = getTotalItems.executeQuery();
            while(rs.next())
            {
                totalItems = rs.getInt(1);
            }
        } catch (SQLException ex) {
           ex.printStackTrace();
        }
        return totalItems;
    }

     public int ViewItemAvailable() {
       int Items=0;
        ResultSet rs=null;
        try {
            CallableStatement getTotalItems = ConnectionDB.getConnection().prepareCall("{call sp_ItemAvailable}");
            rs = getTotalItems.executeQuery();
            while(rs.next())
            {
                Items = rs.getInt(1);
            }
        } catch (SQLException ex) {
           ex.printStackTrace();
        }
        return Items;
    }

    public TableModel ViewItem() {
        SetTableModel rtm = new SetTableModel();
        String Query = "select * from Items where I_Status = 1 and I_Quantity>0";
        try {
            rtm.setHostURL(ConnectionDB.getConnectionString());
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        rtm.setQuery(Query);
        return rtm;
    }

    public TableModel ViewItemByDate(String DateTime, int index) {
        SetTableModel rtm = new SetTableModel();
        int orderBy=index;
        String Date=DateTime;
        String Query;
        if(orderBy==0)
        {
            Query = "select B.B_ID,BD.I_ID,BD.Quantity,BD.Price,B.CustID,B.Date,B.Payment,B.Total " +
                    "from Bills as B inner join BillDetails as BD on " +
                    "B.B_ID=BD.B_ID where B.Status = 1 and B.Date="+Date;
        }
        else if(orderBy==1)
        {
            Query = "select B.B_ID,BD.I_ID,BD.Quantity,BD.Price,B.CustID,B.Date,B.Payment,B.Total " +
                    "from Bills as B inner join BillDetails as BD on " +
                    "B.B_ID=BD.B_ID where B.Status = 1 and B.Date="+Date+" order by B.B_ID";
        }
        else if(orderBy==2)
        {
            Query = "select B.B_ID,BD.I_ID,BD.Quantity,BD.Price,B.CustID,B.Date,B.Payment,B.Total " +
                    "from Bills as B inner join BillDetails as BD on " +
                    "B.B_ID=BD.B_ID where B.Status = 1 and B.Date="+Date+" order by BD.Quantity";
        }
        else if(orderBy==3)
        {
            Query = "select B.B_ID,BD.I_ID,BD.Quantity,BD.Price,B.CustID,B.Date,B.Payment,B.Total " +
                    "from Bills as B inner join BillDetails as BD on " +
                    "B.B_ID=BD.B_ID where B.Status = 1 and B.Date="+Date+" order by BD.Price ";
        }
        else if(orderBy==4)
        {
            Query = "select B.B_ID,BD.I_ID,BD.Quantity,BD.Price,B.CustID,B.Date,B.Payment,B.Total " +
                    "from Bills as B inner join BillDetails as BD on " +
                    "B.B_ID=BD.B_ID where B.Status = 1 and B.Date="+Date+" order by B.Total ";
        }
        else
        {
            Query = "select B.B_ID,BD.I_ID,BD.Quantity,BD.Price,B.CustID,B.Date,B.Payment,B.Total " +
                    "from Bills as B inner join BillDetails as BD on " +
                    "B.B_ID=BD.B_ID where Status = 1 ";
        }
        try {
            rtm.setHostURL(ConnectionDB.getConnectionString());
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        rtm.setQuery(Query);
        return rtm;
    }
}
